﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database fpr availability
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {

        /// <summary>
        /// Returns availability according to specified ID
        /// </summary>
        /// <param name="ID">ID of availability</param>
        /// <returns>The Availability</returns>
        public override Availability GetAvailabilityByID(int ID)
        {
            Availability result;
            DbParameter dbAvaID = CreateParameter("ID", ID);

            try
            {
                connection.Open();
                DbDataReader data = GetVector("availability/GetAvailabilityByID", dbAvaID);
                if (!data.Read())
                {
                    throw new Exception("Availability NOT found!");
                }
                else
                {
                    result = new Availability(
                        GetIntByColumn(data, "ID"),
                        GetDateByColumn(data, "FROM"),
                        GetDateByColumn(data, "TO"),
                        GetStringByColumn(data, "NAME"),
                        GetStringByColumn(data, "DESCRIPTION"),
                        GetIntByColumn(data, "USER_ID")
                        );
                }
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Gets all availability records for specific team in defined time range
        /// </summary>
        /// <param name="teamID">ID of team</param>
        /// <param name="from">Start date</param>
        /// <param name="to">End date</param>
        /// <returns>Availability records for team</returns>
        public override List<Availability> GetAvailabilityForTeam(int teamID, DateTime from, DateTime to)
        {
            List<Availability> output = new List<Availability>();

            DbParameter dbteamID = CreateParameter("teamID", teamID);
            DbParameter dbfromDate = CreateParameter("from", from);
            DbParameter dbtoDate = CreateParameter("to", to);
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("availability/GetAvailabilityForTeam", dbteamID, dbfromDate, dbtoDate);

                while (data.Read())
                {
                    Availability ava = new Availability(
                        GetIntByColumn(data,"ID"),
                        GetDateByColumn(data, "FROM"),
                        GetDateByColumn(data, "TO"),
                        GetStringByColumn(data, "NAME"),
                        GetStringByColumn(data, "DESCRIPTION"),
                        GetIntByColumn(data, "USER_ID")
                        );

                    output.Add(ava);
                }
            }
            finally
            {
                connection.Close();
            }
            return output;    
        }

        /// <summary>
        /// Gets all availability records in defined time range
        /// </summary>
        /// <param name="from">Start date</param>
        /// <param name="to">End date</param>
        /// <returns>Availability records </returns>
        public override List<Availability> GetAvailabilities( DateTime from, DateTime to)
        {
            List<Availability> output = new List<Availability>();
            DbParameter dbfromDate = CreateParameter("from", from);
            DbParameter dbtoDate = CreateParameter("to", to);
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("availability/GetAvailabilities", dbfromDate, dbtoDate);
                if (data != null)
                {
                    while (data.Read())
                    {
                        Availability ava = new Availability(
                            GetIntByColumn(data, "ID"),
                            GetDateByColumn(data, "FROM"),
                            GetDateByColumn(data, "TO"),
                            GetStringByColumn(data, "NAME"),
                            GetStringByColumn(data, "DESCRIPTION"),
                            GetIntByColumn(data, "USER_ID")
                            );

                        output.Add(ava);
                    }
                }
            }
            finally
            {
                connection.Close();
            }
            return output;
        }

        /// <summary>
        /// Gets all availability records for specific user in defined time range
        /// </summary>
        /// <param name="userID">ID of user</param>
        /// <param name="from">Start date</param>
        /// <param name="to">End date</param>
        /// <returns>Availability records for user</returns>
        public override List<Availability> GetAvailabilityForUser(int userID, DateTime from, DateTime to)
        {
            List<Availability> output = new List<Availability>();

            DbParameter dbuserID = CreateParameter("userID", userID);
            DbParameter dbfromDate = CreateParameter("from", from);
            DbParameter dbtoDate = CreateParameter("to", to);
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("availability/GetAvailabilityForUser", dbuserID, dbfromDate, dbtoDate);

                while (data.Read())
                {
                    Availability ava = new Availability(
                        GetIntByColumn(data, "ID"),
                        GetDateByColumn(data, "FROM"),
                        GetDateByColumn(data, "TO"),
                        GetStringByColumn(data, "NAME"),
                        GetStringByColumn(data, "DESCRIPTION"),
                        GetIntByColumn(data, "USER_ID")
                        );

                    output.Add(ava);
                }
            }
            finally
            {
                connection.Close();
            }
            return output;
        }
        /// <summary>
        /// Creates new instance of availability
        /// </summary>
        /// <param name="availability">availability</param>
        /// <returns>Created availability</returns>
        public override Availability CreateAvailability(Availability ava)
        {
            DbParameter dbUserID = CreateParameter("userID", ava.User.ID);
            DbParameter dbFrom = CreateParameter("from", ava.FromDateTime);
            DbParameter dbTo = CreateParameter("to", ava.ToDateTime);
            DbParameter dbName = CreateParameter("name", ava.Name);
            DbParameter dbDesc = CreateParameter("desc", ava.Description);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("availability/CreateAvailability", dbUserID, dbFrom, dbTo, dbName, dbDesc);
                if (data != 1)
                {
                    throw new Exception("Availability NOT created!");
                }
                ava.ID = Convert.ToInt32(GetScalar("availability/GetLastInsertedAvailabilityID"));
            }
            finally
            {
                connection.Close();
            }
            return ava;
        }

        /// <summary>
        /// Updates Availability
        /// </summary>
        /// <param name="availability">Start</param>
        public override void UpdateAvailability(Availability ava)
        {
            DbParameter dbID = CreateParameter("ID", ava.ID);
            DbParameter dbUserID = CreateParameter("userID", ava.User.ID);
            DbParameter dbFrom = CreateParameter("from", ava.FromDateTime);
            DbParameter dbTo = CreateParameter("to", ava.ToDateTime);
            DbParameter dbName = CreateParameter("name", ava.Name);
            DbParameter dbDesc = CreateParameter("desc", ava.Description);
            try
            {
                connection.Open();
				int data = ExecuteNonQuery("availability/UpdateAvailability", dbID, dbUserID, dbFrom, dbTo, dbName, dbDesc);
                if (data != 1)
                {
                    throw new Exception("Availability NOT updated!");
                }
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes specific availability
        /// </summary>
        /// <param name="ID">ID of availability</param>
        public override void DeleteAvailability(int ID)
        {
            DbParameter dbID = CreateParameter("ID", ID);

            try
            {
                connection.Open();
                int data = ExecuteNonQuery("availability/DeleteAvailability", dbID);
                if (data != 1)
                {
                    throw new Exception("Availability NOT deleted!");
                }
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
